/******************************************************************************
Paper: The Impact of Welfare on Intergroup Relations
Author: Akshay Dixit

REDS: This .do file analyzes the REDS 1999 data to:
	- Produce Table S7 (terms of loans by source) 
	- Compute some stats reported in  the section "Caste and Social Insurance".
******************************************************************************/

clear all

gl data "$identity/data/reds99"

*******************************************************************************

*** Household features ***

u "$data/rd99001.dta", clear

ren q8 state 
ren q11 caste

count
codebook id2	// This is the household ID
codebook vill_id
codebook state	
	// 7,474 households, 253 villages, 16 states
	
keep id2 state caste
save "$data/state&caste.dta", replace
	
*******************************************************************************

*** Borrowing during 1998-99 and outstanding at the beginning of 1998-99 (Deck 125) ***

u "$data/rd99125.dta", clear

count 			// 1687 loans in the data
codebook id2	// 1234 unique households who borrowed

* Merge state, caste
merge m:1 id2 using "$data/state&caste.dta"
keep if _merge == 3		// Drop all households that didn't borrow at all
drop _merge

* Variables of interest
g borrowing_purpose = q9
g borrowing_principal_amount = q16
g borrowing_source = q19
g borrowing_duration = q22
g no_specific_periodicity = (q23 == 1)
g security_offered = (q24 == 1)
g zero_interest_rate = (q18 == 0)

g institutional = (borrowing_source <= 8)
g borrowing_caste = (borrowing_source == 9)
g borrowing_relatives = (q19 == 10)
g borrowing_friends = (q19 == 11)
g borrowing_rel_friends = (borrowing_source == 10 | borrowing_source == 11)
g borrowing_money_lender = (borrowing_source == 16)
g other_non_institutional = (borrowing_source >= 12 & borrowing_source <= 16)

*** Terms of borrowing, by source of loan ***

putexcel set "$analysis/reds_terms_of_loan.xlsx", replace
putexcel B1 = "(1)" C1 = "(2)" D1 = "(3)" E1 = "(4)" F1 = "(5)"
putexcel B2 = "Relatives" C2= "Friends" D2 = "Same caste" E2 = "Other non-institutional" F2 = "Institutional"
putexcel A3 = "Principal amount (Indian Rupees)" A4 = "Duration (months)" A5 = "No specific repayment periodicity"
putexcel A6 = "Zero interest rate" A7 = "Whether any security offered"
putexcel A10 = "No. of loans"
putexcel A11 = "No. of households"

local row = 3
foreach var in borrowing_principal_amount borrowing_duration no_specific_periodicity zero_interest_rate security_offered {
	
	summ `var' if borrowing_relatives == 1
	putexcel B`row' = (r(mean))
	
	summ `var' if borrowing_friends == 1
	putexcel C`row' = (r(mean))
	
	summ `var' if borrowing_caste == 1
	putexcel D`row' = (r(mean))
	
	summ `var' if other_non_institutional == 1
	putexcel E`row' = (r(mean))
	
	summ `var' if institutional == 1
	putexcel F`row' = (r(mean))
	
local ++row
	
}

* Number of borrowings, by source
	count if borrowing_relatives == 1
	putexcel B10 = (r(N))
	count if borrowing_friends == 1
	putexcel C10 = (r(N))
	count if borrowing_caste == 1
	putexcel D10 = (r(N))
	count if other_non_institutional == 1
	putexcel E10 = (r(N))
	count if institutional == 1
	putexcel F10 = (r(N))

* Number of households, by borrowing source
	distinct id2 if borrowing_relatives == 1
	putexcel B11 = (r(ndistinct))
	distinct id2 if borrowing_friends == 1
	putexcel C11 = (r(ndistinct))
	distinct id2 if borrowing_caste == 1
	putexcel D11 = (r(ndistinct))
	distinct id2 if other_non_institutional == 1
	putexcel E11 = (r(ndistinct))
	distinct id2 if institutional == 1
	putexcel F11 = (r(ndistinct))

	
	/*
		CALCULATION BASED ON THE NUMBERS IN THE ABOVE TABLE:
		
		Out of 1,687 loans, nearly half (47.54%) were from non-institutional sources.
		The modal non-institutional source was relatives (15.77%) followed by moneylenders (11.08%),
		friends (9.48%) and caste members (4.74%). 
		
		There were 266 loans from relatives, 80 loans from caste members and 
		160 loans from friends. That's a total of 506 loans.
		
		Now, suppose that all relatives are caste members (a fair assumption).
		Suppose that 33% (53) loans from friends are loans from caste members.
		So a total of 319 loans are within-caste. 
		=> 63% of all (506) loans from relatives, friends, and caste members are from 
		within-caste relatives and friends.
		
		If we assume that *all* loans from friends within-caste, this fraction 
		jumps to 84%.
		
		Excluding moneylenders, there are 109 loans from other non-institutional sources.
		As a conservative estimate, categorize all of them as within caste borrowings. 
		We then have 615 loans, including 266 loans from relatives, and 53-160 from friends of the same caste.
		Loans from relatives of friends now account for 52-69%.
		
		Other non-institutional sources include Employer (13), Landlord (14) any person 
		to whom the borrower is serving as dhobi, barber, etc. (12), and Other (15) . 
	*/
	
*******************************************************************************
	
*** Borrowing: Reshape data to household level ***

keep id2 borrowing_* no_specific_periodicity security_offered zero_interest_rate
bysort id2: g loan = _n
reshape wide borrowing_* no_specific_periodicity security_offered zero_interest_rate, i(id2) j(loan)

egen num_borrowings = rownonmiss(borrowing_purpose*)
egen num_borrowings_caste = rowtotal(borrowing_caste*)
egen num_borrowings_rel = rowtotal(borrowing_relatives*)
egen num_borrowings_friends = rowtotal(borrowing_friends*)

forvalues i = 1/6 {
	g borrowing_rel_fri`i' = (borrowing_relatives`i' == 1 | borrowing_friends`i' == 1)
	g borrowing_rel_fri_amount`i' = (borrowing_principal_amount`i' * borrowing_rel_fri`i')
	g borrowing_rel_amount`i' = (borrowing_principal_amount`i' * borrowing_relatives`i')
	g borrowing_fri_amount`i' = (borrowing_principal_amount`i' * borrowing_friends`i')
	g borrowing_caste_amount`i' = (borrowing_principal_amount`i' * borrowing_caste`i')
}

egen total_borrowing = rowtotal(borrowing_principal_amount*)
egen total_rel_borrowing = rowtotal(borrowing_rel_amount*)
egen total_fri_borrowing = rowtotal(borrowing_fri_amount*)
egen total_rel_fri_borrowing = rowtotal(borrowing_rel_fri_amount*)
egen total_caste_borrowing = rowtotal(borrowing_caste_amount*)

g any_borrowing_caste = (num_borrowings_caste > 0)
g any_borrowing_rel = (num_borrowings_rel > 0)
g any_borrowing_friends = (num_borrowings_friends > 0)
g borrowing_rel_friends = (any_borrowing_rel == 1 | any_borrowing_friends == 1)	

keep id2 *borrowing*
save "$data/borrowings_deck125.dta", replace

*******************************************************************************

*** Gifts received during 1998-99 (Deck 129) ***
	
u "$data/rd99129.dta", clear

drop if q12 == 1	// Drop inheritances, keep only gifts
drop if q13 == 3	// Drop dowry

count
codebook id2
tab q6	
tab q8 if q8>0, sort
tab q11
tab q13
tab q15 if q15>0
tab q15 if q15>0, sort

g gift_amount = q11
sum gift_amount, d

g gift_source = q13

g gift_bloodrelation = (q13 == 1)
replace gift_bloodrelation = . if q13 == .
g gift_maritalrelation = (q13 == 2)
replace gift_maritalrelation = . if q13 == .
g gift_others = (q13 == 4)
replace gift_others = . if q13 == .
	
keep id2 gift_*
bysort id2: g gift = _n
reshape wide gift_*, i(id2) j(gift)
egen gift_num = rownonmiss(gift_source*)

forvalues i = 1/10 {
	g gift_relation`i' = (gift_bloodrelation`i' == 1 | gift_maritalrelation`i' == 1)
	g gift_relation_amount`i' = (gift_amount`i' * gift_relation`i')
	g gift_maritalrelation_amount`i' = (gift_amount`i' * gift_maritalrelation`i')
}

egen gift_relation_total = rowtotal(gift_relation_amount*)
egen gift_total = rowtotal(gift_amount*)

save "$data/gifts_deck129.dta", replace

*******************************************************************************

*** Gift of consumer durable received during 1998-99 (Deck 117) ***

u "$data/rd99117.dta", clear

drop if q8 == 1 	// Drop inheritances
drop if q11 == 3	// Drop dowry

count
codebook id2

g durables_amount = q10
g durables_bloodrelation = (q11 == 1)
g durables_maritalrelation = (q11 == 2)

keep id2 durables_*
bysort id2: g durable = _n
reshape wide durables_*, i(id2) j(durable)

forvalues i = 1/8 {
	g durables_relation`i' = (durables_bloodrelation`i' == 1 | durables_maritalrelation`i' == 1)
	g durables_relation_amount`i' = (durables_amount`i' * durables_relation`i')
	g durables_maritalrelation_amount`i' = (durables_amount`i' * durables_maritalrelation`i')
}

egen durables_relation_total = rowtotal(durables_relation_amount*)
egen durables_total = rowtotal(durables_amount*)

keep id2 durables_*
egen durables_num = rownonmiss(durables_amount*)

save "$data/durables_deck117.dta", replace

*******************************************************************************

*** Household level analysis ***

u "$data/rd99001.dta", clear

merge 1:1 id2 using "$data/borrowings_deck125.dta", gen(merge_borrowings)
replace num_borrowings = 0 if num_borrowings == .

merge 1:1 id2 using "$data/gifts_deck129.dta", gen(merge_gifts)
replace gift_num = 0 if gift_num == .

merge 1:1 id2 using "$data/durables_deck117.dta", gen(merge_durables)
replace durables_num = 0 if durables_num == .

* Recode missing values: Here it just means that the household has not borrowed/received a gift
foreach var in total_rel_borrowing total_caste_borrowing gift_relation_total durables_relation_total {
	replace `var' = 0 if `var' == .
}

* Check if loans and gifts from caste members are complements 
	// Exclude friends because gifts data does not report friends
g whether_gift = (gift_relation_total > 0)
g whether_loan = (total_caste_borrowing > 0 | total_rel_borrowing > 0)
reg whether_gift whether_loan, vce(robust)

* Fraction of debt that relatives, friends, caste members account for

	// Compute the sum of all loan amounts across all households
egen sum_total_borrowing = total(total_borrowing)

	// Compute the sum of loan amounts from relatives, friends, caste members
egen sum_total_rel_borrowing = total(total_rel_borrowing)
egen sum_total_fri_borrowing = total(total_fri_borrowing)
egen sum_total_caste_borrowing = total(total_caste_borrowing)
	
	// Compute proportions using the above sum totals spanning all households
g prop_relatives_allborrowing = sum_total_rel_borrowing/sum_total_borrowing
g prop_caste_allborrowing = sum_total_caste_borrowing/sum_total_borrowing
g prop_fri_allborrowing = sum_total_fri_borrowing/sum_total_borrowing

summ prop_relatives_allborrowing prop_caste_allborrowing prop_fri_allborrowing
	
*******************************************************************************

	// Delete intermediate datasets, clear
cap erase "$data/state&caste.dta"
cap erase "$data/borrowings_deck125.dta"
cap erase "$data/gifts_deck129.dta"
cap erase "$data/durables_deck117.dta"

clear



